/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package facade;

import entity.Groups;
import entity.Role;
import entity.Users;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.annotation.Resource;
import javax.ejb.EJB;
import javax.ejb.SessionContext;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import javax.sql.DataSource;
import javax.transaction.UserTransaction;
import oracle.jdbc.OracleTypes;

/**
 *
 * @author Svetlana Shikhutskaya
 */
@Stateless
public class UsersFacade extends AbstractFacade<Users> implements UsersFacadeLocal {

    @EJB
    private RoleFacadeLocal roleFacade;
    @Resource
    UserTransaction transaction;
    @PersistenceContext(unitName = "Delta-ejbPU")
    private EntityManager em;
    @Resource
    protected SessionContext ctx;
    @Resource
    UserTransaction tx;
    @Resource(mappedName = "jdbc/delta")
    private DataSource ds;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public UsersFacade() {
        super(Users.class);
    }

    public boolean createUser(Users user, String[] roles) {
        boolean temp = false;
        String roleName = "";
        Users u = readByLogin(user.getLogin());
        if (u == null) {
            if (roles == null) {
                roleName = "Customer";
                temp = createUserDefault(user, roleName);
            } else {
                if (roles.length >= 1) {
                    temp=createUserWithRoles(user, roles);
                }
            }
        }
        return temp;
    }

    private boolean createUserWithRoles(Users user, String[] roles) {
        boolean temp = false;
        ArrayList<Role> listRole = new ArrayList<Role>();
        try {
            for (int i = 0; i < roles.length; i++) {
                Role role = roleFacade.readByName(roles[i]);
                listRole.add(role);
            }
            user.setRoleCollection(listRole);
            em.persist(user);
            for (int i = 0; i < roles.length; i++) {
                listRole.get(i).getUsersCollection().add(user);
                em.merge(listRole.get(i));
            }
            temp = true;
        } catch (NoResultException e) {
            Logger.getLogger(UsersFacade.class.getName()).log(Level.SEVERE, null, e);
        }
        return temp;
    }

    private boolean createUserDefault(Users user, String roleName) {
        boolean temp=false;
        try {
            Role role = roleFacade.readByName(roleName);
            Collection<Role> c = new ArrayList<Role>();
            c.add(role);
            user.setRoleCollection(c);
            role.getUsersCollection().add(user);
            em.persist(user);
            em.merge(role);
            temp=true;
        } catch (NoResultException e) {
            Logger.getLogger(UsersFacade.class.getName()).log(Level.SEVERE, null, e);
        }
        return temp;
    }

    public Users readByLogin(String login) {
        Query query = em.createNamedQuery("Users.findByLogin");
        query.setParameter("login", login);
        Users user = null;
        try {
            user = (Users) query.getSingleResult();
        } catch (NoResultException ex) {
            return user;
        }
        return user;
    }

    @Override
    public List<Users> findRangeSortBy(String sortBy, int offset, int noOfRecords) {
        return getEntityManager().createNamedQuery("Users.SortBy" + sortBy).setFirstResult(offset).setMaxResults(noOfRecords).getResultList();
    }

    @Override
    public List<Users> findRangeSortBy(int pageNum, String orderBy) {
        Connection con = null;
        ResultSet rs = null;
        CallableStatement cstmt = null;
        List<Users> users = new ArrayList();
        try {
            con = ds.getConnection();
            //call stored procedure
            cstmt = con.prepareCall("{call getUsers(?,?,?)}");
            //set parameters

            if (orderBy.equalsIgnoreCase("LastName")) {
                cstmt.setInt("orderBy", 1);
            } else if (orderBy.equalsIgnoreCase("FirstName")) {
                cstmt.setInt("orderBy", 2);
            } else if (orderBy.equalsIgnoreCase("Email")) {
                cstmt.setInt("orderBy", 3);
            } else if (orderBy.equalsIgnoreCase("Login")) {
                cstmt.setInt("orderBy", 4);
            }
            cstmt.setInt("page_num", pageNum);
            cstmt.registerOutParameter("p_refcur", OracleTypes.CURSOR);
            cstmt.executeUpdate();
            //get ResultSet
            rs = (ResultSet) cstmt.getObject("p_refcur");
            //make List of Orders
            while (rs.next()) {
                Users user = new Users(
                        
                        rs.getString("Login"),
                        rs.getInt("idUser"),
                        rs.getString("firstName"),
                        rs.getString("lastName"),
                        rs.getString("EMail"),
                        rs.getString("Address")
                        );

                users.add(user);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CategoryFacade.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                //close Recordset,CallableStatement, Connection
                if (rs != null) {
                    rs.close();
                }
                if (cstmt != null) {
                    cstmt.close();
                }
                if (con != null) {
                    con.close();
                }
                return users;
            } catch (SQLException ex) {
                Logger.getLogger(CategoryFacade.class.getName()).log(Level.SEVERE, null, ex);
                return users;
            }
        }
    }

    @Override
    public Collection<Groups> findGroupByUser(Integer idUser) {
        Users usr = find(idUser);
        Collection<Groups> grk = usr.getGroupsCollection();
        return grk;
    }

    @Override
    public Collection<Role> findRoleByUser(Integer idUser) {
        Users usr = find(idUser);
        Collection<Role> grk = usr.getRoleCollection();
        return grk;
    }

    @Override
    public List<Users> findRange(int offset, int noOfRecords) {

        return getEntityManager().createNamedQuery("Users.findAll").setFirstResult(offset).setMaxResults(noOfRecords).getResultList();

    }

    @Override
    public int count() {
        return Integer.parseInt(getEntityManager().createNamedQuery("Users.countRows").getSingleResult().toString());
    }

    public List<Users> findByKeyword(int offset, int noOfRecords, String keyword) {

        List<Users> results = null;
        try {
            TypedQuery<Users> query = em.createNamedQuery("Users.findByKeyword", Users.class);
            query.setParameter("keyword", "%" + keyword + "%");
            query.setFirstResult(offset);
            query.setMaxResults(noOfRecords);
            results = query.getResultList();
        } catch (NoResultException nre) {
            // Ignore this because as per our logic this is ok!
        }
        return results;
    }
//    @Override
//    public int count(String status) { 
//        return Integer.parseInt(getEntityManager().createNamedQuery("Orders.countByStatus").setParameter("orderStatus",status).getSingleResult().toString());
//
//    }

    @Override
    public List<Users> findByRole(int id_role) {
        List<Users> users;
        users = em.createNativeQuery(Users.getFindByRoleQuery(), Users.class)
                .setParameter(1, id_role).setParameter(2, id_role)
                .getResultList();
        return users;
    }
}
